package com.best.oasis.settlement.util;

import java.io.FileInputStream;
import java.io.IOException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.Time;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;

import com.best.oasis.settlement.common.enums.apaccount.ApAccountDetailType;
import com.best.oasis.settlement.common.vo.supplier.SupplierVO;

public class APBalanceImport {
	
	public void importAccountDetail(Connection con, String fileName) throws Exception{
		FileInputStream inputStream = null;
		try {
			inputStream = new FileInputStream(fileName);
			POIFSFileSystem fs = new POIFSFileSystem(inputStream);
			HSSFWorkbook wb = new HSSFWorkbook(fs);
			HSSFSheet sheet = wb.getSheetAt(0);

			int index = 1;
			HSSFRow row = sheet.getRow(index);
			if(row==null){
				Logger.log("行" + index + "：数据为空。");
				return;
			}
			List<ApBalance> balances = new ArrayList<ApBalance>();
			while (row != null) {
				//读取excel中的数据
				ApBalance balance = createBalance(con, row);
				if(balance != null)
					balances.add(balance);
				row = sheet.getRow(++index);
			}
			if(balances.size() != (index - 1)){
				throw new Exception("AP余额数据校验错误，错误行数：" + (index - 1 - balances.size()));
			}
			//账户信息
			StringBuffer sql = new StringBuffer("insert into st_apaccountdetail(id,code,optlock,creator,createtime,amount, effectivedate, ")
			.append("invoiceno,matchcode,refcode, remainamount, remark,type, supplier_id) values (?,?,?,?,?,?,?,?,?,?,?,?,?,?)");
			PreparedStatement ps = con.prepareStatement(sql.toString());

			for(ApBalance balance : balances){
				//插入预借款申请
				insertApply(ps, balance);
				//插入付款记录
				insertpayment(ps, balance);
				//更新供应商余额
//				不需要，全部为零。
//				SupplierImport.(con, invoice.getPayCustomer().getId(), invoice.getPendingChargeOfAmount());
			}
			
		} catch (Exception e) {
			throw e;
		} finally{
			try {
				inputStream.close();
			} catch (IOException e) {
			}
		}
	}
	//读取excel中数据
	//格式：id1	id2	供应商编码	供应商名称	借款申请日期	实际付款日期	剩余金额	备注
	private ApBalance createBalance(Connection con, HSSFRow row) throws Exception{
		ApBalance vo = new ApBalance();
		//id1
		vo.setId1(new Long(getCellText(row.getCell(0))));
		//id2
		vo.setId2(new Long(getCellText(row.getCell(1))));
		//供应商
		String supplierCode = getCellText(row.getCell(2));
		SupplierVO supplier = SupplierImport.getSupplier(con, supplierCode);
		if(supplier == null){
			Logger.log("供应商在系统中不存在：" + supplierCode,row.getRowNum());
			return null;
		}
		String supplierName = getCellText(row.getCell(3));
		if(!supplier.getName().equals(supplierName)){
			Logger.log("供应商编码名字不匹配：" + supplier.getName(),row.getRowNum());
			return null;
		}
		vo.setSupplierId(supplier.getId());
		//申请日期
		vo.setApplyDate(new SimpleDateFormat("yyyy-MM-dd").parse(getCellText(row.getCell(4))));
		//支付日期
		vo.setPaymentDate(new SimpleDateFormat("yyyy-MM-dd").parse(getCellText(row.getCell(5))));
		//剩余金额
		vo.setAmount(new Double(getCellText(row.getCell(6))));
		//备注
		vo.setRemark(getCellText(row.getCell(7)));
		return vo;
	}
	//插入申请数据。
	private void insertApply(PreparedStatement ps, ApBalance vo) throws SQLException{
		ps.setLong(1,vo.getId1());
		ps.setString(2, "AP"+vo.getId1());
		ps.setInt(3,0);
		ps.setString(4,"admin");
		ps.setTime(5, new Time(System.currentTimeMillis()));
		//金额
		ps.setDouble(6, vo.getAmount());
		//生效日期，即申请日期
		ps.setDate(7, new java.sql.Date(vo.getApplyDate().getTime()));
		//发票号码
		ps.setString(8, "");
		//匹配参考号
		ps.setString(9, "");
		ps.setString(10,"");
		//余额
		ps.setDouble(11, vo.getAmount());
		ps.setString(12, vo.getRemark());
		//预借款申请
		ps.setString(13, ApAccountDetailType.ADVANCE_CASH_REQUEST.toString());
		ps.setLong(14, vo.getSupplierId());
		ps.executeUpdate();
	}
	//插入付款数据。
	private void insertpayment(PreparedStatement ps, ApBalance vo) throws SQLException{
		//id2
		ps.setLong(1,vo.getId2());
		ps.setString(2, "P"+vo.getId2());
		ps.setInt(3,0);
		ps.setString(4,"admin");
		ps.setTime(5, new Time(System.currentTimeMillis()+1000));
		//金额
		ps.setDouble(6, (0 - vo.getAmount()));
		//生效日期，即申请日期
		ps.setDate(7, new java.sql.Date(vo.getApplyDate().getTime()));
		//发票号码
		ps.setString(8, "");
		//匹配参考号
		ps.setString(9, "");
		//付款时，对应的申请流水号
		ps.setString(10,"AP"+vo.getId1());
		//余额
		ps.setDouble(11, (0 - vo.getAmount()));
		ps.setString(12, vo.getRemark());
		//实际付款
		ps.setString(13, ApAccountDetailType.PAYMENT.toString());
		ps.setLong(14, vo.getSupplierId());
		ps.executeUpdate();
	}
	/**
	 * 获取当前CELL内容
	 */
	private static String getCellText(HSSFCell cell) {
		if (cell == null)
			return "";
		int type = cell.getCellType();
		switch (type) {
		case HSSFCell.CELL_TYPE_BLANK:
			return "";
		case HSSFCell.CELL_TYPE_NUMERIC:
			double value = cell.getNumericCellValue();
			String tep = null;
			if (HSSFDateUtil.isCellDateFormatted(cell)) {    
		        Date date = HSSFDateUtil.getJavaDate(value);
		        tep = new SimpleDateFormat("yyyy-MM-dd").format(date);
		    }else{
				tep = Double.toString(value);
				if(tep.endsWith(".0")){
					tep = tep.substring(0, tep.length() - 2);
				}
		    }
			return tep; 
		case HSSFCell.CELL_TYPE_STRING:
			return cell.getRichStringCellValue().getString().trim();
		}
		return "";
	}
}

class ApBalance{
	private Long id1;
	private Long id2;
	private Long supplierId;
	private Date applyDate;
	private Date paymentDate;
	private Double amount;
	private String remark;
	public Long getSupplierId() {
		return supplierId;
	}
	public void setSupplierId(Long supplierId) {
		this.supplierId = supplierId;
	}
	public Date getApplyDate() {
		return applyDate;
	}
	public void setApplyDate(Date applyDate) {
		this.applyDate = applyDate;
	}
	public Date getPaymentDate() {
		return paymentDate;
	}
	public void setPaymentDate(Date paymentDate) {
		this.paymentDate = paymentDate;
	}
	public Double getAmount() {
		return amount;
	}
	public void setAmount(Double amount) {
		this.amount = amount;
	}
	public String getRemark() {
		return remark;
	}
	public void setRemark(String remark) {
		this.remark = remark;
	}
	public Long getId1() {
		return id1;
	}
	public void setId1(Long id1) {
		this.id1 = id1;
	}
	public Long getId2() {
		return id2;
	}
	public void setId2(Long id2) {
		this.id2 = id2;
	}
	
}